--SELECT * FROM FN_GET_WIP_PROCESS_FLOW_VIEW_INFO ( 44 )
ALTER FUNCTION  FN_GET_WIP_PROCESS_FLOW_VIEW_INFO( @PRODUCT_ID INT )
RETURNS @FN_RESULTS TABLE (	PROCESSSTEP_ID BIGINT,
				PART_NUMBER VARCHAR (50),
			  	PS_TYPE VARCHAR(50),
				PS_TYPE_NAME VARCHAR (20),
				WIP_TOTAL_QTY INT,
				LOT_NUMBER VARCHAR(50),
				LOCATION VARCHAR(100),
				LOT_QTY INT,
				LOT_STATUS VARCHAR(20),
				FAB_LOT_NUMBER VARCHAR(50),
				LOT_CATEGORY VARCHAR(20)  )
AS
BEGIN
	DECLARE @PROCESSSTEPS_TABLE TABLE ( TEMP_ID BIGINT IDENTITY, PROCESSSTEP_ID BIGINT )
	DECLARE @RESULTS_TABLE  TABLE (	PROCESSSTEP_ID BIGINT,
					PART_NUMBER VARCHAR (50),
				  	PS_TYPE VARCHAR(50),
					PS_TYPE_NAME VARCHAR (20),
					WIP_TOTAL_QTY INT,
					LOT_NUMBER VARCHAR(50),
					LOCATION VARCHAR(100),
					LOT_QTY INT,
					LOT_STATUS VARCHAR(20),
					FAB_LOT_NUMBER VARCHAR(50),
					LOT_CATEGORY VARCHAR(20) )
	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @TEMP_PRODUCT_ID INT
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(20)
	DECLARE @WIP_TOTAL_QTY INT
	DECLARE @NEXT_PROCESSSTEPS_CURSOR CURSOR
	DECLARE @TEMP_ID BIGINT
	DECLARE @SORT_PROCESSSTEP_ID BIGINT
	
	SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0 )
	 BEGIN
		INSERT INTO @PROCESSSTEPS_TABLE ( PROCESSSTEP_ID ) VALUES ( @PROCESSSTEP_ID )
	 END

	SELECT TOP 1 @TEMP_ID = TEMP_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE
	WHILE ( @TEMP_ID IS NOT NULL AND @TEMP_ID > 0 )
	 BEGIN
		DELETE FROM @PROCESSSTEPS_TABLE WHERE TEMP_ID = @TEMP_ID
		IF ( (SELECT COUNT(*) FROM @RESULTS_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID ) = 0 )
		 BEGIN
			SELECT @TEMP_PRODUCT_ID = A.PRODUCT_ID,@PART_NUMBER = A.PART_NUMBER,
				@PS_TYPE = B.PS_TYPE,@PS_TYPE_NAME = B.PS_TYPE_NAME 
				FROM PRODUCT A, PROCESS_STEP B 
				WHERE B.PROCESSSTEP_ID = @PROCESSSTEP_ID 
				AND A.PROCESSSTEP_ID = B.PROCESSSTEP_ID
			IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'FAB' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  WAFER_QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_Fab_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'Wafer Bank' )
			 BEGIN
				    INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  WAFER_QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_WaferStore_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'Fab Option' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  WAFER_QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_Fab_Option_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END			
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'Die Bank' )
			 BEGIN
				    INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_DieBank_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'Assembly' )
			 BEGIN
			    	INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_Assembly_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'FT' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_Test_FT_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'FPO' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_Test_FPO_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END		
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'Tape & Reel' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_ClassStore_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END
			ELSE IF ( UPPER(LTRIM(RTRIM(@PS_TYPE))) = 'FG' )
			 BEGIN
				INSERT INTO @RESULTS_TABLE
				    SELECT  PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, PS_TYPE_NAME, 0 AS WIP_TOTAL_QTY, LOT_NUMBER, L_VENDOR_NAME,  QTY_IN AS QTY_IN, LOT_CURRENT_STATUS, FAB_LOT_NUMBER, LOT_CATEGORY 
				             FROM vw_FG_Wip_View
				             WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			 END 
			 IF ( @@ROWCOUNT = 0 )
			  BEGIN
			    INSERT INTO @RESULTS_TABLE VALUES ( @PROCESSSTEP_ID, @PART_NUMBER, @PS_TYPE, @PS_TYPE_NAME, 0, NULL, NULL, NULL, NULL, NULL, NULL )
			  END
			DECLARE @TEMP_WIP_TOTAL_QTY INT
			SET @TEMP_WIP_TOTAL_QTY = 0
			SELECT @TEMP_WIP_TOTAL_QTY = ISNULL (SUM ( LOT_QTY ),0) FROM @RESULTS_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			UPDATE @RESULTS_TABLE SET WIP_TOTAL_QTY = @TEMP_WIP_TOTAL_QTY WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		 END

		DECLARE @TEMP_PROCESSSTEP_ID BIGINT
		SET @NEXT_PROCESSSTEPS_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE NEXT_PROCESSSTEP_ID = @PROCESSSTEP_ID
		OPEN @NEXT_PROCESSSTEPS_CURSOR
		FETCH NEXT FROM @NEXT_PROCESSSTEPS_CURSOR INTO @TEMP_PROCESSSTEP_ID
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0 )
			 BEGIN
				IF ( (SELECT COUNT(*) FROM @RESULTS_TABLE WHERE PROCESSSTEP_ID = @TEMP_PROCESSSTEP_ID ) = 0 )
				 BEGIN
					INSERT INTO @PROCESSSTEPS_TABLE ( PROCESSSTEP_ID ) VALUES ( @TEMP_PROCESSSTEP_ID )
				 END
			 END
			SET @TEMP_PROCESSSTEP_ID = NULL
			FETCH NEXT FROM @NEXT_PROCESSSTEPS_CURSOR INTO @TEMP_PROCESSSTEP_ID
		 END
		CLOSE @NEXT_PROCESSSTEPS_CURSOR
		DEALLOCATE @NEXT_PROCESSSTEPS_CURSOR
		SET @TEMP_ID = NULL
		SET @PROCESSSTEP_ID = NULL
		SELECT TOP 1 @TEMP_ID = TEMP_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE
	 END

	INSERT @FN_RESULTS SELECT * FROM @RESULTS_TABLE ORDER BY LOT_NUMBER 
	RETURN
END